﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;
using Sweepids.Models.Object;

namespace Sweepids.Models.Dao
{
    public class ItemDao : Dao
    {
        public Item FindWinnerIfNeeded(Item item)
        {
            try
            {
                //Check if winuser doesn't exist and end time is past current time
                //var myCommand = new OracleCommand("SELECT * FROM SWEEP_ITEM, SWEEP_USER " +
                //                                  "WHERE SWEEP_ITEM.WIN_USER = SWEEP_USER.USERNAME " +
                //                                  "AND ITEM_ID=:ID", myConnection);
                //var myReader = RunCommand(myCommand, item.OracleParameters, true);
                //var checkItem = new Item();
                //while (myReader.Read())
                //{
                //    checkItem = new Item(myReader);
                //}
                var checkItem = item;
                var hasWinner = checkItem.WinUser != "";
                if (!hasWinner && checkItem.EndTime < DateTime.Now && checkItem.SweepsOnItem < 1)
                {
                    //If expired and no bids extend expire date
                    var itemDao = new ItemDao();
                    checkItem.EndTime = DateTime.Now.AddDays(6);
                    itemDao.Update(checkItem);
                }
                if (!hasWinner && checkItem.EndTime < DateTime.Now)
                {
                    //Calculate winuser
                    var totalSweeps = checkItem.SweepsOnItem;
                    var rnd = new Random();
                    var randomNum = rnd.Next(1, (int) totalSweeps);
                    var winUser = new User();
                    for (var i = 1; i <= checkItem.BidOnsList.Count; i++)
                    {
                        if (checkItem.BidOnsList.Take(i).Sum(b => b.NumBidsForUser) >= randomNum)
                        {
                            winUser = checkItem.BidOnsList[i - 1].UserObj;
                            break;
                        }
                    }
                    //Fill winuser and loseuser list
                    foreach (var bidOn in checkItem.BidOnsList)
                    {
                        if (bidOn.Username == winUser.Username)
                        {
                            //add to items winuser
                            item.WinUser = winUser.Username;
                            var itemDao = new ItemDao();
                            itemDao.Update(item);
                        }
                        else
                        {
                            //add to lose table
                            var loseDao = new LoseDao();
                            loseDao.Create(new Lose
                                {
                                    Username = bidOn.Username,
                                    ItemId = bidOn.ItemId
                                });
                        }
                        var bidOnDao = new BidOnDao();
                        bidOnDao.Delete(bidOn);
                    }
                }
                return item;
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::FindWinnerIfNeeded(Item): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Create new Item in the database
        /// </summary>
        /// <param name="item">The item to add</param>
        public void Create(Item item)
        {
            try
            {
                var myCommand = new OracleCommand("INSERT INTO SWEEP_ITEM " +
                                                  "(NAME, PRICE, START_TIME, END_TIME, SWEEPS_ON_ITEM, DESCRIPTION, ORIGINAL_PRICE, WIN_USER, PHOTO, CAT_ID) " +
                                                  "VALUES " +
                                                  "(:NAME, :PRICE, :START_TIME, :END_TIME, :SWEEPS_ON_ITEM, :DESCRIPTION, :ORIGINAL_PRICE, :WIN_USER, :PHOTO, :CAT_ID)", myConnection);
                RunCommand(myCommand, item.OracleParameters, false);
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::Create(Item): " + e.Message);
                throw;
            }
        }

        public List<Item> ReadItemsWon(string username)
        {
            try
            {
                var result = new List<Item>();
                var parameters = new Item { WinUser = username };
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_ITEM " +
                                                  "WHERE WIN_USER=:ID", myConnection);
                var myReader = RunCommand(myCommand, parameters.OracleParameters, true);
                while (myReader.Read())
                {
                    result.Add(new Item(myReader));
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::Read(int): " + e.Message);
                throw;
            }
        }
        public List<Item> ReadAllForCategory(decimal catId)
        {
            try
            {
                var result = new List<Item>();
                var parameters = new Item { CatId = catId};
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_ITEM " +
                                                  "WHERE CAT_ID=:CAT_ID", myConnection);
                var myReader = RunCommand(myCommand, parameters.OracleParameters, true);
                while (myReader.Read())
                {
                    result.Add(new Item(myReader));
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::ReadAllForCategory(string): " + e.Message);
                throw;
            }
        }
        public List<Item> ReadAll()
        {
            try
            {
                var result = new List<Item>();
                
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_ITEM ORDER BY END_TIME, NAME, PRICE ASC", myConnection);
                var myReader = RunCommand(myCommand, true);
                while (myReader.Read())
                {
                    result.Add(new Item(myReader));
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::ReadAll(): " + e.Message);
                throw;
            }
        }

        public List<Item> ReadUserWins(string username)
        {
            try
            {
                var result = new List<Item>();
                var parameters = new Item { WinUser = username };
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_ITEM " +
                                                  "WHERE WIN_USER=:WIN_USER", myConnection);
                var myReader = RunCommand(myCommand, parameters.OracleParameters, true);
                while (myReader.Read())
                {
                    result.Add(new Item(myReader));
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::Read(int): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Gets an Item
        /// </summary>
        /// <param name="itemId">The id of the item to get</param>
        /// <returns>An Item</returns>
        public Item Read(decimal itemId)
        {
            try
            {
                var result = new Item { Id = itemId };
                var myCommand = new OracleCommand("SELECT * FROM SWEEP_ITEM " +
                                                  "WHERE ITEM_ID=:ID", myConnection);
                var myReader = RunCommand(myCommand, result.OracleParameters, true);
                while (myReader.Read())
                {
                    result = new Item(myReader);
                }
                myReader.Close();
                myConnection.Close();
                return result;
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::Read(int): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Update Item in the database
        /// </summary>
        /// <param name="item">The Item to update</param>
        public void Update(Item item)
        {
            try
            {
                var myCommand = new OracleCommand("UPDATE SWEEP_ITEM SET " +
                                                  "NAME=:NAME, " +
                                                  "PRICE=:PRICE, " +
                                                  "START_TIME=:START_TIME, " +
                                                  "END_TIME=:END_TIME, " +
                                                  "SWEEPS_ON_ITEM=:SWEEPS_ON_ITEM, " +
                                                  "DESCRIPTION=:DESCRIPTION, " +
                                                  "ORIGINAL_PRICE=:ORIGINAL_PRICE, " +
                                                  "WIN_USER=:WIN_USER, " +
                                                  "PHOTO=:PHOTO, " +
                                                  "CAT_ID=:CAT_ID " +
                                                  "WHERE ITEM_ID=:ID", myConnection);
                RunCommand(myCommand, item.OracleParameters, false);
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::Update(Item): " + e.Message);
                throw;
            }
        }

        /// <summary>
        /// Delete item from the database
        /// </summary>
        /// <param name="item">The Item to delete</param>
        public void Delete(Item item)
        {
            try
            {
                var myCommand = new OracleCommand("DELETE FROM SWEEP_ITEM " +
                                                 "WHERE ITEM_ID=:ID", myConnection);
                RunCommand(myCommand, item.OracleParameters, false);
            }
            catch (Exception e)
            {
                Debug.WriteLine("ItemDao::Delete(Item): " + e.Message);
                throw;
            }
        }
    }
}